﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;

public partial class Secured_ViewExpenses : System.Web.UI.Page
{
    SqlConnection conn;
    SqlCommand cmd;
    SqlDataAdapter da;
    DataSet ds;
    SqlDataReader dr;
    protected void Page_Load(object sender, EventArgs e)
    {
        conn = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["Connstr"].ConnectionString);
        if (conn.State != ConnectionState.Open)
        {
            try
            {
                conn.Open();
            }
            catch (SqlException ex)
            {
                ltMsg.Text = ex.Message;
            }
        }
        if (!Page.IsPostBack)
        {
            FillYear(DateTime.Today.Year);
            FillExpenseHeads();
            FillGrid();
        }
    }
    private void FillYear(int CurrentYear)
    {
        cmbYear.Items.Clear();
        while (CurrentYear <= 2020)
        {
            cmbYear.Items.Add(CurrentYear.ToString());
            CurrentYear += 1;
        }
    }
    private void FillGrid()
    {
        cmd = new SqlCommand("SELECT ExpenseDate,PaidBy,ForPeriod,ExpenseHead,ExpenseDesc,ExpenseAmount,ExpenseID from HAT_Expenses", conn);
        ds = new DataSet("dsExpensesList");
        da = new SqlDataAdapter(cmd.CommandText, conn);
        try
        {
            da.Fill(ds, "NewTable");
            grdExpenses.DataSource = ds.Tables["NewTable"].DefaultView;
            grdExpenses.DataBind();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
        FindSum();
    }
    private void FindSum()
    {
        cmd = new SqlCommand("Select sum(ExpenseAmount) from HAT_Expenses", conn);
        try
        {
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                txtTotal.Text = dr.GetValue(0).ToString();
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void FillExpenseHeads()
    {
        cmbExpenseHead.Items.Clear();
        cmd = new SqlCommand("Select ExpenseHead from HAT_ExpenseHead order by ExpenseHead asc", conn);
        try
        {
            dr = cmd.ExecuteReader();
            cmbExpenseHead.Items.Add(string.Empty);
            while (dr.Read())
            {
                cmbExpenseHead.Items.Add(dr.GetString(0));
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void FindSum(string ExpenseHead)
    {
        cmd = new SqlCommand("Select sum(ExpenseAmount) from HAT_Expenses where ExpenseHead=@Head", conn);
        cmd.Parameters.Add("@Head", SqlDbType.VarChar).Value = ExpenseHead;
        try
        {
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                txtTotal.Text = dr.GetValue(0).ToString();
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void FillGrid(string ExpenseHead)
    {
        cmd = new SqlCommand("SELECT ExpenseID, ExpenseDate,PaidBy,ForPeriod,ExpenseHead,ExpenseDesc,ExpenseAmount from HAT_Expenses where ExpenseHead='" + ExpenseHead + "'", conn);
        ds = new DataSet("dsExpensesList");
        da = new SqlDataAdapter(cmd.CommandText, conn);
        try
        {
            da.Fill(ds, "NewTable");
            grdExpenses.DataSource = ds.Tables["NewTable"].DefaultView;
            grdExpenses.DataBind();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
        FindSum(ExpenseHead);
    }
    private void FillGrid(int ExpenseMonth, int ExpenseYear)
    {
        string MonthYear = "0" + Convert.ToString(ExpenseMonth) + "/" + Convert.ToString(ExpenseYear);
        cmd = new SqlCommand("select ExpenseID, ExpenseDate, PaidBy, ForPeriod, ExpenseHead, ExpenseDesc, ExpenseAmount from HAT_Expenses where ForPeriod='" + MonthYear + "'", conn);
        //cmd.Parameters.Add("@ExpensePeriod", SqlDbType.VarChar).Value = MonthYear;
        ds = new DataSet("dsExpensesList");
        da = new SqlDataAdapter(cmd.CommandText, conn);
        try
        {
            da.Fill(ds, "NewTable");
            grdExpenses.DataSource = ds.Tables["NewTable"].DefaultView;
            grdExpenses.DataBind();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
        FindSum(ExpenseMonth, ExpenseYear);
    }
    protected void FindSum(int ExpenseMonth, int ExpenseYear)
    {
        string MonthYear = "0" + Convert.ToString(ExpenseMonth) + "/" + Convert.ToString(ExpenseYear);
        cmd = new SqlCommand("Select sum(ExpenseAmount) from HAT_Expenses where ForPeriod=@ExpenseMonth", conn);
        cmd.Parameters.Add("@ExpenseMonth", SqlDbType.VarChar).Value = MonthYear;
        try
        {
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                txtTotal.Text = dr.GetValue(0).ToString();
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    protected void cmbExpenseHead_SelectedIndexChanged(object sender, EventArgs e)
    {
        FillGrid(cmbExpenseHead.SelectedItem.ToString());
    }
    protected void cmbMonths_SelectedIndexChanged(object sender, EventArgs e)
    {
        FillGrid((cmbMonths.SelectedIndex + 1), Convert.ToInt32(cmbYear.SelectedValue));
    }
    protected void grdExpenses_RowEditing(object sender, GridViewEditEventArgs e)
    {
        grdExpenses.EditIndex = e.NewEditIndex;
        FillGrid();
    }
    protected void grdExpenses_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        grdExpenses.EditIndex = -1;
        FillGrid();
    }
    protected void grdExpenses_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

    }
    protected void lnkExport_Command(object sender, CommandEventArgs e)
    {
        if (e.CommandName.Equals("Export"))
        {
            string attachment = "attachment;filename=Export.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            HtmlForm frm = new HtmlForm();
            grdExpenses.Parent.Controls.Add(frm);
            frm.Attributes["runat"] = "server";
            frm.Controls.Add(grdExpenses);
            //frm.RenderControl(htw);
            grdExpenses.Attributes["runat"] = "server";
            grdExpenses.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();
        }
    }
    protected void grdExpenses_RowDataBound(object sender, GridViewRowEventArgs e)
    {
    }
    protected void grdExpenses_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Type cstype = this.GetType();
        GridViewRow row = (GridViewRow)grdExpenses.Rows[e.RowIndex];
        int EID = Int32.Parse(grdExpenses.DataKeys[e.RowIndex].Value.ToString());
        cmd = new SqlCommand("DELETE FROM HAT_Expenses WHERE ExpenseID=@ID", conn);
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = EID;
        try
        {
            Page.ClientScript.RegisterClientScriptBlock(cstype, "DeleteExpense", "alert('Selected Expense will be Deleted')", true);
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
        FillGrid();
    }
}
